<?php
/**
 * TÃªn file mÃ¬nh cÃ³ thá»ƒ tá»± chá»�n nhÆ°ng nÃªn Ä‘áº·c theo tÃªm controller sáº½ dá»… quáº£n lÃ½
 */

/**
 * TÃªn class: Example_Models_Dao_Pdo_Mysql_Example(Tenmodule_Models_Dao_Pdo_Mysql_Tenfile)
 */

class Enterprise_Models_Dao_Pdo_Mysql_Enterprise extends Tomato_Model_Dao
    implements Enterprise_Models_Interface_Enterprise
{
    public function convert($entity)
    {
        return new Enterprise_Models_Enterprise($entity);
    }

    public function add($enterprise){
        $this->_conn->insert($this->_prefix . 'enterprise',
                            array(
                                'frontend_active'        => $enterprise->frontend_active,
                                'backend_active'         => $enterprise->backend_active,
                                'company_name_vn'        => $enterprise->company_name_vn,
                                'company_name_en'        => $enterprise->company_name_en,
                                'contact_email'          => $enterprise->contact_email,
                                'contact_tel'            => $enterprise->contact_tel,
                                'contact_fax'            => $enterprise->contact_fax,
                                'contact_address'        => $enterprise->contact_address,
                                'bank_name_vnd'          => $enterprise->bank_name_vnd,
                                'bank_name_usd'          => $enterprise->bank_name_usd,
                                'bank_account_vnd'       => $enterprise->bank_account_vnd,
                                'bank_account_usd'       => $enterprise->bank_account_usd,
                                'bank_account_owner_vnd' => $enterprise->bank_account_owner_vnd,
                                'bank_account_owner_usd' => $enterprise->bank_account_owner_usd,
                                'registration_number'    => $enterprise->registration_number,
                                'registration_date'      => $enterprise->registration_date,
                                'registration_city'      => $enterprise->registration_city,
                                'deleted'                => $enterprise->deleted,
                                'professions_id'         => $enterprise->professions_id,
                                'currency'               => $enterprise->currency,
                                'application'            => $enterprise->application,
                            ));
        return $this->_conn->lastInsertId($this->_prefix . 'enterprise');
    }

    public function find($offset, $count, $exp = null)
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix . 'enterprise'));
        if ($exp) {
            if (isset($exp['id'])) {
                $select->where('e.id = ?', $exp['id']);
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }
            if (isset($exp['backend_active'])) {
                $select->where('e.backend_active = ?', $exp['backend_active']);
            }			
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
            if (isset($exp['company_name'])) {
                $select->where('e.company_name_vn LIKE "%'.$exp['company_name'].'%"');
            }			
            if (isset($exp['registration_number'])) {
                $select->where('e.registration_number LIKE "%'.$exp['registration_number'].'%"');
            }
			if(isset($exp["registration_date_from"])){
				$select->where('e.registration_date >= "'.$exp['registration_date_from'].'"');
			}
			if(isset($exp["registration_date_to"])){
				$select->where('e.registration_date <= "'.$exp['registration_date_to'].'"');
			}			
			//profession
            if (isset($exp['profession'])) {
                $select->where('e.professions_id LIKE "%,'.$exp['profession'].',%"');
            }
            if (isset($exp['application'])) {
				if(empty($exp['application']))
					$select->where('e.application != 1');
				else 
					$select->where('e.application = 1');
            }
        }
        $rs = $select->order('e.id DESC')
                    ->limit($count, $offset)
                    ->query()
                    ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function count($exp = null)
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix . 'enterprise'), array('num_enterprise' => 'COUNT(*)'));
        if ($exp) {
            if (isset($exp['id'])) {
                $select->where('e.id = ?', $exp['id']);
            }
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }
            if (isset($exp['backend_active'])) {
                $select->where('e.backend_active = ?', $exp['backend_active']);
            }	
            if (isset($exp['company_name'])) {
                $select->where('e.company_name_vn LIKE "%'.$exp['company_name'].'%"');
            }			
            if (isset($exp['registration_number'])) {
                $select->where('e.registration_number LIKE "%'.$exp['registration_number'].'%"');
            }
			if(isset($exp["registration_date_from"])){
				$select->where('e.registration_date >= "'.$exp['registration_date_from'].'"');
			}
			if(isset($exp["registration_date_to"])){
				$select->where('e.registration_date <= "'.$exp['registration_date_to'].'"');
			}		
			//profession
            if (isset($exp['profession'])) {
                $select->where('e.professions_id LIKE "%,'.$exp['profession'].',%"');
            }
            if (isset($exp['application'])) {
				if(empty($exp['application']))
					$select->where('e.application != 1');
				else 
					$select->where('e.application = 1');
            }			
		}
        return $select->query()->fetch()->num_enterprise;
    }

    public function getById($id,$deleted=true){
        $select = $this->_conn
                    ->select()
                    ->from(array('e' => $this->_prefix . 'enterprise'))
                    ->where('e.id = ?', $id);
		if($deleted)					
             $select->where('e.deleted != ?', 1);
		$row   = $select->limit(1)
						->query()
						->fetch();		
        return (null == $row) ? null : new Enterprise_Models_Enterprise($row);
    }

    public function update($enterprise)
    {
        return $this->_conn->update($this->_prefix . 'enterprise',
                                array(
                                    'frontend_active'        => $enterprise->frontend_active,
                                    'backend_active'         => $enterprise->backend_active,
                                    'company_name_vn'        => $enterprise->company_name_vn,
                                    'company_name_en'        => $enterprise->company_name_en,
                                    'contact_email'          => $enterprise->contact_email,
                                    'contact_tel'            => $enterprise->contact_tel,
                                    'contact_fax'            => $enterprise->contact_fax,
                                    'contact_address'        => $enterprise->contact_address,
                                    'bank_name_vnd'          => $enterprise->bank_name_vnd,
                                    'bank_name_usd'          => $enterprise->bank_name_usd,
                                    'bank_account_vnd'       => $enterprise->bank_account_vnd,
                                    'bank_account_usd'       => $enterprise->bank_account_usd,
                                    'bank_account_owner_vnd' => $enterprise->bank_account_owner_vnd,
                                    'bank_account_owner_usd' => $enterprise->bank_account_owner_usd,
                                    'registration_number'    => $enterprise->registration_number,
                                    'registration_date'      => $enterprise->registration_date,
                                    'registration_city'      => $enterprise->registration_city,
                                    'deleted'                => $enterprise->deleted,
                                    'professions_id'         => $enterprise->professions_id,
                                    'currency'               => $enterprise->currency,
                                    'application'            => $enterprise->application,
                                ),
                                array(
                                    'id = ?' => $enterprise->id,
                                ));
								
    }

    public function delete($enterprise){
        return $this->_conn->update($this->_prefix . 'enterprise',
                                array(
                                    'deleted'               => 1,
                                ),
                                array(
                                    'id = ?' => $enterprise->id,
                                ));
    }

    public function getProfessions(){
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix . 'enterprise_professions'))
                        ;
        $rs = $select->query()
                     ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function updateProfessions($professions){
        $sql = "Update {$this->_prefix}enterprise_professions set enterprise_id = " .
                "concat(enterprise_id,'{$professions['enterprise_id']},') where professions_id = {$professions['professions_id']}";
        return $this->_conn->query($sql);
    }

    public function updateProfessions1($professions){
        $sql = "Update {$this->_prefix}enterprise_professions set enterprise_id = " .
                "REPLACE(enterprise_id,',{$professions['enterprise_id']},',',') where professions_id = {$professions['professions_id']}";
        return $this->_conn->query($sql);
    }

    public function getProfessionByIds($strIds){
        $ids = explode(',', $strIds);
        if(count($ids) > 2){
            $where = 'professions_id = '. $ids[1];
            for ($i = 2; $i < count($ids) - 1 ; $i++ ){
                $where .= ' OR professions_id = '. $ids[$i];
            }
            $sql  = "SELECT *
                FROM " . $this->_prefix . "enterprise_professions
                WHERE $where";
            $rs  = $this->_conn->query($sql)->fetchAll();
            return new Tomato_Model_RecordSet($rs, $this);
        }
        return null;
    }

    /*=========================Front end=====================*/

    public function getAdvertise($count){
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix.'enterprise'))
                        ->joinInner(array('md' => $this->_prefix.'enterprise_market_data'), "e.id = md.enterprise_id", array())
                        ->where('md.advertise = ?', 1)
						->where('e.frontend_active = 1')
						->where('e.deleted != ?', 1)
                        ->limit($count, 0)
                        ;
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getMostViewed($count){
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix.'enterprise'))
                        ->joinLeft(array('md' => $this->_prefix.'enterprise_market_data'), "e.id = md.enterprise_id", array())
						->where('e.frontend_active = 1')
						->where('e.deleted != ?', 1)
                        ->limit($count, 0)
                        ->order('md.view_count DESC')
                        ;
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function getMostBuy($count){
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix.'enterprise'))
                        ->joinInner(array('p' => $this->_prefix.'shop_product'), "p.enterprise_id = e.id", array('product_id'))
                        ->joinInner(array('md' => $this->_prefix.'shop_product_market_data'), "p.product_id = md.product_id", array('buy_count'))
                        ->where('md.buy_count is not null')
						->where('e.frontend_active = 1')
						->where('e.deleted != ?', 1)
                        ->order('md.buy_count DESC')
                        ;
        $rs = $select->query()->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }
	
	public function getMarketData($enterprise_id){
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix.'enterprise_market_data'))
                        ->where('e.enterprise_id = ?', $enterprise_id)
                        ->limit(1);
        $row = $select->query()->fetchAll();
        $enterprises = new Tomato_Model_RecordSet($row, $this);
        return (count($enterprises) == 0) ? null : $enterprises[0];
    }	
    public function addMarketData($enterprise){
        $data = array(
            'enterprise_id'  => $enterprise->enterprise_id,
            'view_count' 	 => $enterprise->view_count
        );
        if(isset($enterprise->view_count)){
            $data['view_count'] = $enterprise->view_count;
        }
        return $this->_conn->insert($this->_prefix.'enterprise_market_data', $data);
    }

    public function updateMarketData($enterprise){
        $where[] = "enterprise = {$enterprise->enterprise_id}";
        $sql = "UPDATE `{$this->_prefix}enterprise_market_data` SET `view_count` = `view_count`+{$enterprise->view_count} WHERE `enterprise_id` = {$enterprise->enterprise_id}";
        return $this->_conn->query($sql);
    }

    /**=========search function news===================*/
    public function basic($offset, $count, $exp = null, $type = 'basic')
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix . 'enterprise'))
                        ;
        if ($exp) {
            if($type == 'basic'){
                $select->joinLeft(array('ep' => $this->_prefix . 'enterprise_professions'), "e.professions_id LIKE concat('%,',ep.professions_id,',%')", array());
                if (isset($exp['keyword'])) {
                    //$select->where("match(e.company_name_vn, e.company_name_en, e.contact_address, e.registration_city, ep.professions_name, ep.professions_name_en) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("e.company_name_vn like '%{$exp['keyword']}%' OR e.company_name_en like '%{$exp['keyword']}%' OR e.contact_address like '%{$exp['keyword']}%' OR e.registration_city like '%{$exp['keyword']}%' OR ep.professions_name like '%{$exp['keyword']}%' OR ep.professions_name_en like '%{$exp['keyword']}%'");
                }
            }else{
                $select->joinInner(array('ep' => $this->_prefix . 'enterprise_professions'), "e.professions_id LIKE concat('%,',ep.professions_id,',%')", array());
                if (isset($exp['keyword'])) {
                    //$select->where("match(e.company_name_vn, e.company_name_en, e.contact_address, e.registration_city, ep.professions_name, ep.professions_name_en) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("e.company_name_vn like '%{$exp['keyword']}%' OR e.company_name_en like '%{$exp['keyword']}%'");
                }
            }
            if (isset($exp['contact_address'])) {
                $select->where("e.contact_address like '%{$exp['contact_address']}%'");
            }
            if(isset($exp['registration_city'])){
                $select->where("e.registration_city like '%{$exp['registration_city']}%'");
            }
            if(isset($exp['professions_id'])){
                $select->where('ep.professions_id = ?', $exp['professions_id']);
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }			
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
        }
        $select->order('e.id DESC')
                ->limit($count, $offset)
                ->group('e.id')
                ;
        $rs = $select->query()
                    ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function basiccount($exp = null, $type = 'basic')
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('e' => $this->_prefix . 'enterprise'), array('id'))
                        ;
        if ($exp) {
            if($type == 'basic'){
                $select->joinLeft(array('ep' => $this->_prefix . 'enterprise_professions'), "e.professions_id LIKE concat('%,',ep.professions_id,',%')", array());
                if (isset($exp['keyword'])) {
                    //$select->where("match(e.company_name_vn, e.company_name_en, e.contact_address, e.registration_city, ep.professions_name, ep.professions_name_en) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("e.company_name_vn like '%{$exp['keyword']}%' OR e.company_name_en like '%{$exp['keyword']}%' OR e.contact_address like '%{$exp['keyword']}%' OR e.registration_city like '%{$exp['keyword']}%' OR ep.professions_name like '%{$exp['keyword']}%' OR ep.professions_name_en like '%{$exp['keyword']}%'");
                }
            }else{
                $select->joinInner(array('ep' => $this->_prefix . 'enterprise_professions'), "e.professions_id LIKE concat('%,',ep.professions_id,',%')", array());
                if (isset($exp['keyword'])) {
                    //$select->where("match(e.company_name_vn, e.company_name_en, e.contact_address, e.registration_city, ep.professions_name, ep.professions_name_en) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("e.company_name_vn like '%{$exp['keyword']}%' OR e.company_name_en like '%{$exp['keyword']}%'");
                }
            }
            if (isset($exp['contact_address'])) {
                $select->where("e.contact_address like '%{$exp['contact_address']}%'");
            }
            if(isset($exp['registration_city'])){
                $select->where("e.registration_city like '%{$exp['registration_city']}%'");
            }
            if(isset($exp['professions_id'])){
                $select->where('ep.professions_id = ?', $exp['professions_id']);
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }						
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
        }
        $select->group('e.id');
        $rs = $select->query()->fetchAll();
        return count($rs);
    }

    public function info($offset, $count, $exp = null, $type = 'basic')
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('i' => $this->_prefix . 'enterprise_info'))
                        ->joinInner(array('e' => $this->_prefix . 'enterprise'),"i.enterprise_id = e.id", array('id','company_name_vn', 'company_name_en'))
                        ;
        if ($exp) {
            if($type == 'basic'){
                if (isset($exp['keyword'])) {
                    //$select->where("match(i.info_title, i.info_string) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("i.info_title LIKE '%{$exp['keyword']}%' OR i.info_string LIKE '%{$exp['keyword']}%'");
                }
            }else{
                if (isset($exp['keyword'])) {
                    $select->where("i.info_title LIKE '%{$exp['keyword']}%' OR i.info_string LIKE '%{$exp['keyword']}%'");
                }
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }						
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
        }
        $select->where("i.language = ?", $this->_lang)
                ->order('e.id DESC')
                ->limit($count, $offset)
                ;
        $rs = $select->query()
                    ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function infocount($exp = null, $type = 'basic')
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('i' => $this->_prefix . 'enterprise_info'), array('num_info' => "COUNT(*)"))
                        ->joinInner(array('e' => $this->_prefix . 'enterprise'),"i.enterprise_id = e.id", array())
                        ;
        if ($exp) {
            if($type == 'basic'){
                if (isset($exp['keyword'])) {
                    //$select->where("match(i.info_title, i.info_string) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("i.info_title LIKE '%{$exp['keyword']}%' OR i.info_string LIKE '%{$exp['keyword']}%'");
                }
            }else{
                if (isset($exp['keyword'])) {
                    $select->where("i.info_title LIKE '%{$exp['keyword']}%' OR i.info_string LIKE '%{$exp['keyword']}%'");
                }
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }			
			
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
        }
        $select->where("i.language = ?", $this->_lang);
        return $select->query()->fetch()->num_info;
    }

    public function blog($offset, $count, $exp = null, $type = 'basic')
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('b' => $this->_prefix . 'enterprise_blog'))
                        ->joinInner(array('e' => $this->_prefix . 'enterprise'),"b.enterprise_id = e.id", array('id','company_name_vn', 'company_name_en'))
                        ;
        if ($exp) {
            if($type == 'basic'){
                if (isset($exp['keyword'])) {
                    //$select->where("match(b.title, b.content) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("b.title LIKE '%{$exp['keyword']}%' OR b.content LIKE '%{$exp['keyword']}%'");
                }
            }else{
                if (isset($exp['keyword'])) {
                    $select->where("b.title LIKE '%{$exp['keyword']}%' OR b.content LIKE '%{$exp['keyword']}%'");
                }
            }
            if (isset($exp['from_date'])) {
                $select->where('b.date >= ?', $exp['from_date']);
            }
            if (isset($exp['to_date'])) {
                $select->where('b.date <= ?', $exp['to_date']);
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }						
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
        }
        $select->where("b.language = ?", $this->_lang)
                ->order('e.id DESC')
                ->limit($count, $offset)
                ;
        $rs = $select->query()
                    ->fetchAll();
        return new Tomato_Model_RecordSet($rs, $this);
    }

    public function blogcount($exp = null, $type = 'basic')
    {
        $select = $this->_conn
                        ->select()
                        ->from(array('b' => $this->_prefix . 'enterprise_blog'), array('num_blog' => "COUNT(*)"))
                        ->joinInner(array('e' => $this->_prefix . 'enterprise'),"b.enterprise_id = e.id", array())
                        ;
        if ($exp) {
            if($type == 'basic'){
                if (isset($exp['keyword'])) {
                    //$select->where("match(b.title, b.content) against('{$exp['keyword']}' IN BOOLEAN MODE)");
                    $select->where("b.title LIKE '%{$exp['keyword']}%' OR b.content LIKE '%{$exp['keyword']}%'");
                }
            }else{
                if (isset($exp['keyword'])) {
                    $select->where("b.title LIKE '%{$exp['keyword']}%' OR b.content LIKE '%{$exp['keyword']}%'");
                }
            }
            if (isset($exp['from_date'])) {
                $select->where('b.date >= ?', $exp['from_date']);
            }
            if (isset($exp['to_date'])) {
                $select->where('b.date <= ?', $exp['to_date']);
            }
            if (isset($exp['frontend_active'])) {
                $select->where('e.frontend_active = ?', $exp['frontend_active']);
            }						
            if (isset($exp['deleted'])) {
                $select->where('e.deleted = ?', $exp['deleted']);
            }
        }
        $select->where("b.language = ?", $this->_lang);
        return $select->query()->fetch()->num_blog;
    }
}
